package Messages;

import java.io.IOException;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;

import ocsf.server.ConnectionToClient;
import server.SQLConnection;
/**
 * this class implements the activitiy report from the DB 
 * @author Ron Shenkerman
 * @author Gabi Revah
 * @author Aviad Almog
 * @author Ivan Onyshchenko
 */
public class MessageGenerateActivity {

	
	private ConnectionToClient client;
	private int active; 
	private int locked; 
	private int freeze; 
	private int lend; 
	private int notintime; 
	private int row1;
	/**
	 * the constractor of this class 
	 * @param client
	 */
	public MessageGenerateActivity(ConnectionToClient client) {
		this.client= client;
		
	}
	
	/**
	 * generate report from the DB 
	 * @param str
	 * @throws SQLException
	 * @throws IOException
	 */
	public void Generate (String[] str) throws SQLException, IOException{

		SQLConnection sc = new SQLConnection();
		PreparedStatement active = sc.getConnection().prepareStatement("SELECT sum(active) FROM db1.activity WHERE date >= '"+str[1]+"' and date <= '"+str[2]+"';");
		PreparedStatement lock = sc.getConnection().prepareStatement("SELECT sum(locked) FROM db1.activity WHERE date >= '"+str[1]+"' and date <= '"+str[2]+"';");
		PreparedStatement freeze = sc.getConnection().prepareStatement("SELECT sum(freeze) FROM db1.activity WHERE date >= '"+str[1]+"' and date <= '"+str[2]+"';");
		PreparedStatement lend = sc.getConnection().prepareStatement("SELECT sum(lend) FROM db1.activity WHERE date >= '"+str[1]+"' and date <= '"+str[2]+"';");
		PreparedStatement notintime = sc.getConnection().prepareStatement("SELECT sum(notintime) FROM db1.activity WHERE date >= '"+str[1]+"' and date <= '"+str[2]+"';");
		PreparedStatement resultac = sc.getConnection().prepareStatement("INSERT INTO db1.activemonth (idactivemonth,active,locked,freeze,lend,notintime,date) VALUES(?,?,?,?,?,?,?);");
		PreparedStatement numoflines = sc.getConnection().prepareStatement("SELECT * FROM db1.activemonth;");

		sc.getConnection().setAutoCommit(true);
		
		
		java.util.Date today = new java.util.Date();
		java.sql.Date timestamp = new java.sql.Date(today.getTime());
		
		
		java.sql.ResultSet rsactive =  active.executeQuery();
		rsactive.next();
		this.active = rsactive.getInt(1);
		
		java.sql.ResultSet rslock =  lock.executeQuery();
		rslock.next();
		locked = rslock.getInt(1);
		
		java.sql.ResultSet rsfreeze =  freeze.executeQuery();
		rsfreeze.next();
		this.freeze = rsfreeze.getInt(1);		
		
		java.sql.ResultSet rslend =  lend.executeQuery();
		rslend.next();
		this.lend = rslend.getInt(1);
						
		java.sql.ResultSet rsnotintime =  notintime.executeQuery();
		rsnotintime.next();
		this.notintime = rsnotintime.getInt(1);
		
		
		
		java.sql.ResultSet rs=  numoflines.executeQuery();
		row1 = 1;
		while(rs.next()){
			int temp = rs.getInt(1);
				if(row1>temp)
					row1=row1;
					else{
					row1=temp;
					}
		
		}
		
		resultac.setInt(1, this.row1+1);
		resultac.setInt(2, this.active);
		resultac.setInt(3, locked);
		resultac.setInt(4, this.freeze);
		resultac.setInt(5, this.lend);
		resultac.setInt(6, this.notintime);
		resultac.setDate(7,timestamp);
		
		resultac.executeUpdate();
		
		ArrayList<Object> Arr=new ArrayList<Object>();
		Arr.add("Generate");
		Arr.add(this.active);
		Arr.add(this.locked);
		Arr.add(this.freeze);
		Arr.add(this.lend);
		Arr.add(this.notintime);
	
		client.sendToClient(Arr);
	}

	
	
	
	
	
	
	
	
	
}
